package com.fpt.group3.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import com.fpt.group3.form.Government;
import com.fpt.group3.service.exception.SystemException;
/**
 * Ger data from database.
 * @author Spatra
 *
 */
public class GovernmentDAOImpl implements GovernmentDAO {
    @Override
    public final LinkedList<Government> listGovernment(final boolean active)
            throws SystemException {
        LinkedList<Government> listGov = new LinkedList<Government>();
        String sql = "";
        if (active) {
            sql = "SELECT * FROM ab_sd.govofficeregion "
                    + "INNER JOIN county "
                    + "ON govofficeregion.countyid = county.countyid "
                    + "WHERE STATUS = 1";
        } else {
            sql = "SELECT * FROM ab_sd.govofficeregion "
                    + "INNER JOIN county "
                    + "ON govofficeregion.countyid = county.countyid";
        }
        Statement stmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            Connection con = conn.open();
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            try {
                while (rs.next()) {
                    try {
                        String status = "";
                        if (rs.getBoolean("STATUS")) {
                            status = "Yes";
                        } else {
                            status = "No";
                        }
                        listGov.add(new Government(
                                rs.getInt("GOVOFFICEREGIONID"),
                                rs.getString("DESCRIPTION"),
                                rs.getInt("COUNTYID"),
                                rs.getString("COUNTYNAME"),
                                rs.getString("GOVOFFICEREGIONNAME"),
                                status));
                    } catch (NullPointerException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return listGov;
        } catch (SystemException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return listGov;
    }
    @Override
    public final LinkedList<String> getListCounty(
                 final String govOfficeRegionName) {
        LinkedList<String> listCounty = new LinkedList<String>();
        String sql = "Select county.COUNTYNAME "
                + "from govofficeregion inner join county "
                + "on govofficeregion.COUNTYID = county.COUNTYID "
                + "where govofficeregion.GOVOFFICEREGIONNAME = ?";
        ConnectDB conn = null;
        try {
            conn = new ConnectDB();
        } catch (SystemException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.open().prepareStatement(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SystemException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println(govOfficeRegionName);
        ResultSet rs = null;
        try {
            pstmt.setString(1, govOfficeRegionName);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                listCounty.add(rs.getString(1));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return listCounty;
    }
}
